package database

import (
	"strconv"
	"strings"
)

// sql builder object
type Sql struct {
	Table  string
	Column string
	where  []string
	join   []string
	joinOn map[string]string
	order  string
	limit  string
}

// get Sql object
func NewSql(table string, column... string) *Sql {
	var col string
	if len(column) < 1 || column[0] == "" {
		col = "*"
	}else {
		col = strings.Join(column, ",")
	}
	sql := &Sql{Table:table, Column:col}
	return sql
}

// add where string
func (this *Sql) Where(str string) *Sql {
	this.where = append(this.where, str)
	return this
}

// add join string
func (this *Sql) Join(table string, on string) *Sql {
	if this.joinOn == nil {
		this.joinOn = make(map[string]string)
	}
	this.join = append(this.join, table)
	this.joinOn[table] = on
	return this
}

// add order string
func (this *Sql) Order(order string) *Sql {
	this.order = order
	return this
}

// add limit params
func (this *Sql) Limit(limit int, offset... int) *Sql {
	var str string
	if len(offset) < 1 {
		str = strconv.Itoa(limit)
	}else {
		str = strconv.Itoa(limit) + "," + strconv.Itoa(offset[0])
	}
	this.limit = str
	return this
}

// add paged params
func (this *Sql) Page(page int, size int) *Sql {
	if page < 1 {
		page = 1
	}
	this.limit = strconv.Itoa((page - 1)*size) + "," + strconv.Itoa(size)
	return this
}

// build join string
func (this *Sql) buildJoin() string {
	if len(this.join) < 1 {
		return ""
	}
	str := ""
	for _, v := range this.join {
		if this.joinOn[v] == "" {
			str += " LEFT JOIN " + v
		}
		str += " LEFT JOIN " + v + " ON " + this.joinOn[v]
	}
	return str
}

// build where string, AND
func (this *Sql) buildWhere() string {
	if len(this.where) > 0 {
		return " WHERE " + strings.Join(this.where, " AND ")
	}
	return ""
}

// return select query
func (this *Sql) Select() string {
	sql := "SELECT " + this.Column + " FROM " + this.Table
	sql += this.buildJoin()
	sql += this.buildWhere()
	if this.order != "" {
		sql += " ORDER BY " + this.order
	}
	if this.limit != "" {
		sql += " LIMIT " + this.limit
	}
	return sql
}

// return delete query
func (this *Sql) Delete() string {
	sql := "DELETE FROM " + this.Table
	sql += this.buildWhere()
	return sql
}

// return insert query
func (this *Sql) Insert() string {
	if this.Column == "*" {
		return ""
	}
	sql := "INSERT INTO " + this.Table + "(" + this.Column + ") VALUES ("
	colsLength := len(strings.Split(this.Column, ","))
	sql += strings.Repeat("?,", colsLength) + ")"
	return strings.Replace(sql, ",)", ")", -1)
}

// return update query
func (this *Sql) Update() string {
	if this.Column == "*" {
		return ""
	}
	sql := "UPDATE " + this.Table + " SET "
	cols := strings.Split(this.Column, ",")
	for _, col := range cols {
		sql +=  col + " = ?,"
	}
	sql = strings.TrimRight(sql, ",")
	sql += this.buildWhere()
	return sql
}

// return count
func (this *Sql) Count() string {
	this.Column = "count(*) AS countNum"
	return this.Select()
}


